package com.gukun.springboot.tdengine.demo.mybatis.service;

import cn.hutool.core.date.DateTime;
import cn.hutool.json.JSONUtil;
import com.gukun.springboot.tdengine.demo.mybatis.domain.TagData;
import com.gukun.springboot.tdengine.demo.mybatis.domain.TagInfo;
import com.gukun.springboot.tdengine.demo.mybatis.domain.enums.TagType;
import com.taosdata.jdbc.TSDBPreparedStatement;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * 使用原生JDBC进行数据处理
 * 在插入等功能上，减少sql损耗
 */
@Service
public class TDEngineService {

    @Value("${tdengine.url:jdbc:TAOS://127.0.0.1:6030?user=root&password=taosdata}")
    private String url = "";

    @Value("${tdengine.db:syncdb}")
    private String dbName = "";


    public String getDbName() {
        return dbName;
    }

    public void setDbName(String dbName) {
        this.dbName = dbName;
    }

    /**
     * 连接数据库
     *
     * @return
     * @throws SQLException
     */
    private Connection getConnection() throws SQLException {
        return DriverManager.getConnection(this.url);
    }

    /**
     * 压缩方式
     *
     * @param dbName
     */
    public void init(String dbName) {
        this.dbName = dbName;
        dropDatabase();
        createDatabase();
        createSupTable();
    }

    public void dropDatabase() {
        String sql = " drop database if exists " + dbName;
        execute(sql);
    }

    public void createDatabase() {
        String sql = " create database if not exists " + dbName + " keep  3650 duration 10 buffer 256 comp 2 wal_level 1 cachemodel 'last_value' ";
        execute(sql);
    }


    public List<TagData> getReal() {
        String sql = "select last_row(*),tagId from stag  group by tagId";
        execute(sql);

        return new ArrayList<>();
    }

    public void dropTable(String tbName) {
        final String sql = "drop table if exists " + dbName + "." + tbName + "";
        execute(sql);
    }

    /**
     * 这边建立4个表，存储double型，float型，bool型，int型
     */
    public void createSupTable() {
        for (TagType type : TagType.values()) {
            String sql = " create table if not exists " + getSTableName(type) + "(ts timestamp,val double, quality tinyint) tags(tagId bigint)";
            execute(sql);
        }
//        //开关量
//        final String sql2 = " create table if not exists " + dbName + ".stagbool(ts timestamp,val bool, quality tinyint) tags(tagId bigint)";
//        execute(sql2);
//        //float
//        final String sql3 = " create table if not exists " + dbName + ".stagfloat(ts timestamp,val float, quality tinyint) tags(tagId bigint)";
//        execute(sql3);
//        //int
//        final String sql4 = " create table if not exists " + dbName + ".stagint(ts timestamp,val int, quality tinyint) tags(tagId bigint)";
//        execute(sql4);
    }

    /**
     * 创建表
     *
     * @param tbName
     * @param tagId
     * @param type
     */
    public void createTable(String tbName, int tagId, TagType type) {
        String stag = getSTableName(type);
        final String sql = "create table if not exists " + dbName + "." + tbName + " using " + stag + " tags(" + tagId + ")";
        execute(sql);
    }


    /**
     * 获取超表名称
     *
     * @param type
     * @return
     */
    private String getSTableName(TagType type) {
        String stag = "";
        switch (type) {
            case DOUBLE:
            case BOOL:
            case INT:
            case FLOAT:
                stag = dbName + ".stag" + type.name().toLowerCase();
                break;
        }
        return stag;
    }

    /**
     * 批量建表（点）
     *
     * @param tagInfos
     * @throws Exception
     */
    public void createBatchTable(List<TagInfo> tagInfos) throws Exception {
        if (tagInfos.size() > 5000) {
            throw new Exception("无法批量插入大于5000个表,请分批插入。");
        }

        String sql = "create table ";
        for (TagInfo tagInfo : tagInfos) {
            String tbName = tagInfo.getTagName();
            String sTableName = getSTableName(tagInfo.getTagType());
            sql += "if not exists " + dbName + "." + tbName + " using " + sTableName + " tags(" + tagInfo.getTagId() + ")";
        }

        execute(sql);
    }


    /**
     * 批量插入数据
     *
     * @param tagDataMap
     * @param tagType
     * @throws SQLException
     */
    public void insertBatchWithType(Map<String, List<TagData>> tagDataMap
            , TagType tagType) throws SQLException {
        try (Connection conn = getConnection()) {
            setValue(tagDataMap, conn, tagType);
        }
    }

    private void setValue(Map<String, List<TagData>> tagDataMap, Connection conn, TagType type) throws SQLException {
        String psql = "INSERT INTO ? VALUES(?, ?, ?)";

        for (String tagName : tagDataMap.keySet()) {
            try (TSDBPreparedStatement pst = (TSDBPreparedStatement) conn.prepareStatement(psql)) {
                ArrayList<Long> tss = new ArrayList<>(10000000);
                ArrayList vals = new ArrayList<>(1000000);
                ArrayList<Byte> qualities = new ArrayList<>(10000000);
                List<TagData> tagDataList = tagDataMap.get(tagName);
                for (TagData data : tagDataList) {
                    tss.add(data.getTs());
                    vals.add(data.getVal());
                    qualities.add(data.getQuality());
                }
                pst.setTableName(dbName + "." + tagName);
                pst.setTimestamp(0, tss);
                switch (type) {
                    case DOUBLE:
                        pst.setDouble(1, vals);
                        break;
                    case BOOL:
                        pst.setBoolean(1, vals);
                        break;
                    case INT:
                        pst.setInt(1, vals);
                        break;
                    case FLOAT:
                        pst.setFloat(1, vals);
                        break;
                }

                pst.setByte(2, qualities);
                long start1 = System.currentTimeMillis();
                System.out.println(tagName + "开始执行插入......");
                pst.columnDataAddBatch();
                pst.columnDataExecuteBatch();
                long end1 = System.currentTimeMillis();
                System.out.println(tagName + "插入数据用时: " + (end1 - start1));
            }
        }
    }


    /**
     * 查询数据
     *
     * @param tbName
     * @param start
     * @param end
     * @return
     * @throws SQLException
     */
    public List<TagData> select(String tbName, DateTime start, DateTime end) throws SQLException {
        String sql = "select *,tagid from " + dbName + "." + tbName + " where ts>='" + start + "' and ts<='" + end + "'";
        System.out.println("sql" + sql);
        List<TagData> tagDataList = new ArrayList<>();
        try (Connection connection = getConnection()) {
            try (Statement statement = connection.createStatement()) {
                ResultSet resultSet = statement.executeQuery(sql);
                TagData tagData = new TagData();
                while (resultSet.next()) {
                    Long timestamp = resultSet.getLong(1);
                    String valString = resultSet.getString(2);
                    byte aByte = resultSet.getByte(3);
                    Long aLong = resultSet.getLong(4);
                    tagData.setTs(timestamp);
                    tagData.setVal(valString);
                    tagData.setQuality(aByte);
                    tagData.setTagId(aLong);
                    tagDataList.add(tagData);
                }
            } catch (SQLException e) {
                throw new SQLException(e);
            }

            return tagDataList;
        }
    }


    public int count() {
        int size = getAllTables().size();
        return size;
    }


    public List<String> getAllTables() {
        String sql = "show " + dbName + ".tables";
        List<String> tables = new ArrayList<>();
        System.out.println(sql);
        try (Connection connection = getConnection()) {
            try (Statement statement = connection.createStatement()) {
                ResultSet resultSet = statement.executeQuery(sql);
                while (resultSet.next()) {
                    tables.add(resultSet.getString(1));
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return tables;
    }

    private void printSql(String sql, boolean succeed, long cost) {
        System.out.println("[ " + (succeed ? "OK" : "ERROR!") + " ] time cost: " + cost + " ms, execute statement ====> " + sql);
    }

    private void execute(String sql) {
        long start = System.currentTimeMillis();
        try (Connection connection = getConnection()) {
            try (Statement statement = connection.createStatement()) {
                boolean execute = statement.execute(sql);
                long end = System.currentTimeMillis();
                printSql(sql, true, (end - start));
            } catch (SQLException e) {
                long end = System.currentTimeMillis();
                printSql(sql, false, (end - start));
                e.printStackTrace();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    private static void printHelp() {
        System.out.println("Usage: java -jar JDBCDemo.jar -host <hostname>");
        System.exit(0);
    }

}
